/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package AccessPermission;


import Module.ModuleBean;
import SubModule.SubModuleBean;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.Iterator;
import java.util.Vector;

/**
 *
 * @author aamod
 */
public class Query {
    
    
    static Connection con = null;
    
    public static Vector viewAllSubModule(int id) throws SQLException {
        
        Vector<Object> list=new Vector<Object>();
        try {
            con=DBConnection.Connection.getConnection();
             PreparedStatement pre=con.prepareStatement("select * from sub_module left join module on sub_module.module_id=module.module_id where sub_module.module_id=? order by sub_module.sub_module_id");
             pre.setInt(1,id);
             ResultSet rs=pre.executeQuery();
             while(rs.next())
             {
               SubModuleBean sb=new SubModuleBean();
               ModuleBean mb=new ModuleBean();
               sb.setSubModuleId(rs.getInt(1));
               sb.setSubModuleName(rs.getString(2));
               sb.setModuleId(rs.getInt(3));
               sb.setEntryDate(rs.getDate(4));
               mb.setModuleId(rs.getInt(5));
               mb.setModuleName(rs.getString(6));
               mb.setEntryDate(rs.getDate(7));
               sb.setModuleBean(mb);
               list.add(sb);
             }
            }
        catch (Exception e) 
        {
            System.out.println(e);
        }
        finally
        {
            con.close();
        }
        return list;
    }
    
    public static Vector viewAllModule() throws SQLException {
        
        Vector<Object> list=new Vector<Object>();
        try {
            con=DBConnection.Connection.getConnection();
             PreparedStatement pre=con.prepareStatement("select * from module order by module_id");
             ResultSet rs=pre.executeQuery();
             while(rs.next())
             {
               ModuleBean mb=new ModuleBean();
               mb.setModuleId(rs.getInt(1));
               mb.setModuleName(rs.getString(2));
               mb.setEntryDate(rs.getDate(3));
               list.add(mb);
             }
            }
        catch (Exception e) 
        {
            System.out.println(e);
        }
        finally
        {
            con.close();
        }
        return list;
    }
    
    public static int delete(int uid) throws SQLException
    {
        int i=0;
        try
        {
            con=DBConnection.Connection.getConnection();
            PreparedStatement p=con.prepareStatement("select count(*) from user_access_permission where user_id=?");
            p.setInt(1,uid);
            ResultSet rs=p.executeQuery();
            while(rs.next())
            {
            if(rs.getInt(1)==0)
            {
                i=1;
            }
            else
            {
            PreparedStatement pre=con.prepareStatement("delete from user_access_permission where user_id=?");
            pre.setInt(1,uid);
            i=pre.executeUpdate();
            if(i!=0)
            {
                PreparedStatement pre1=con.prepareStatement("update users set user_status=? where user_id=?");
                pre1.setInt(1,2);
                pre1.setInt(2,uid);
                i=pre1.executeUpdate();
            }
            }
            }
        }
        catch(Exception e)
        {
            return i;
        }
        finally
        {
           con.close();
        }
        return i;
    }
    
    public static int add(Vector<Object> list) throws SQLException
    {
        int i=0,uid=0;
        try
        {
           con=DBConnection.Connection.getConnection();
           PreparedStatement pre=con.prepareStatement("insert into user_access_permission values(useraccessid.nextval,?,?,?,?,sysdate)");
           Iterator it=list.iterator();
           while(it.hasNext())
           {
               UserAccessPermissionBean ab=(UserAccessPermissionBean)it.next();
               uid=ab.getUserId();
               pre.setInt(1,ab.getUserId());
               pre.setInt(2,ab.getModuleId());
               pre.setInt(3,ab.getSubModuleId());
               pre.setInt(4,ab.getAcessStatus());
               pre.addBatch();
           }
           int k[]=pre.executeBatch();
           if(k.length==list.size())
           {
               PreparedStatement pre1=con.prepareStatement("update users set user_status=? where user_id=?");
                pre1.setInt(1,1);
                pre1.setInt(2,uid);
                i=pre1.executeUpdate();
           }
           else
           {
               i=0;
           }
        }
        catch(Exception e)
        {
            
        }
        finally
        {
            con.close();
        }
        return i;
    }
    
     public static Vector userAccessPermissionViewAll(int uid) throws SQLException {
        
        Vector<Object> list=new Vector<Object>();
        try {
            con=DBConnection.Connection.getConnection();
             PreparedStatement pre=con.prepareStatement("select * from user_access_permission left join sub_module on user_access_permission.sub_module_id=sub_module.sub_module_id left join module on user_access_permission.module_id=module.module_id where user_id=? order by user_access_permission.sub_module_id");
             pre.setInt(1,uid);
             ResultSet rs=pre.executeQuery();
             while(rs.next())
             {
               UserAccessPermissionBean ub=new UserAccessPermissionBean();
               SubModuleBean sb=new SubModuleBean();
               ModuleBean mb=new ModuleBean();
               ub.setUserAcessId(rs.getLong(1));
               ub.setUserId(rs.getInt(2));
               ub.setModuleId(rs.getInt(3));
               ub.setSubModuleId(rs.getInt(4));
               ub.setAcessStatus(rs.getInt(5));
               ub.setEntryDate(rs.getDate(6));
               sb.setSubModuleName(rs.getString(8));
               ub.setSubModuleBean(sb);
               mb.setModuleName(rs.getString(12));
               ub.setModuleBean(mb);
               list.add(ub);
             }
            }
        catch (Exception e) 
        {
            return list;
        }
        finally
        {
            con.close();
        }
        return list;
    }
     
      public static Vector userAccessPermissionViewAll(int uid,int mid) throws SQLException {
        
        Vector<Object> list=new Vector<Object>();
        try {
            con=DBConnection.Connection.getConnection();
             PreparedStatement pre=con.prepareStatement("select * from user_access_permission where user_id=? and module_id=? and access_permission=? order by sub_module_id");
             pre.setInt(1,uid);
             pre.setInt(2,mid);
             pre.setInt(3,1);
             ResultSet rs=pre.executeQuery();
             while(rs.next())
             {
               UserAccessPermissionBean ub=new UserAccessPermissionBean();
               ub.setUserAcessId(rs.getLong(1));
               ub.setUserId(rs.getInt(2));
               ub.setModuleId(rs.getInt(3));
               ub.setSubModuleId(rs.getInt(4));
               ub.setAcessStatus(rs.getInt(5));
               ub.setEntryDate(rs.getDate(6));
               list.add(ub);
             }
            }
        catch (Exception e) 
        {
            return list;
        }
        finally
        {
            con.close();
        }
        return list;
    }
      
      public static Vector userAccessPermissionView(int uid,int mid) throws SQLException {
        
        Vector<Object> list=new Vector<Object>();
        try {
            con=DBConnection.Connection.getConnection();
             PreparedStatement pre=con.prepareStatement("select * from user_access_permission where user_id=? and module_id=? order by sub_module_id");
             pre.setInt(1,uid);
             pre.setInt(2,mid);
             ResultSet rs=pre.executeQuery();
             while(rs.next())
             {
               UserAccessPermissionBean ub=new UserAccessPermissionBean();
               ub.setUserAcessId(rs.getLong(1));
               ub.setUserId(rs.getInt(2));
               ub.setModuleId(rs.getInt(3));
               ub.setSubModuleId(rs.getInt(4));
               ub.setAcessStatus(rs.getInt(5));
               ub.setEntryDate(rs.getDate(6));
               list.add(ub);
             }
            }
        catch (Exception e) 
        {
            return list;
        }
        finally
        {
            con.close();
        }
        return list;
    }
      
      public static boolean getUserStatus(String email) throws SQLException
      {
          boolean i=false;
          try
          {
              con=DBConnection.Connection.getConnection();
              PreparedStatement pre=con.prepareStatement("select user_status from users where email=?");
              pre.setString(1,email);
              ResultSet rs=pre.executeQuery();
              while(rs.next())
              {
                  if(rs.getInt(1)==1)
                  {
                      return true;
                  }
                  else
                  {
                      return false;
                  }
              }
          }
          catch(Exception e)
          {
              
          }
          finally
          {
              con.close();
          }
          return i;
      }
      
       public static boolean getAccessStatus(int uid,int module_no,String module) throws SQLException
      {
          boolean i=false;
          PreparedStatement pre=null;
          try
          {
              con=DBConnection.Connection.getConnection();
              if(module_no==2)
              {
              pre=con.prepareStatement("select access_permission from user_access_permission where user_id=? and  sub_module_id=(select sub_module_id from sub_module where sub_module_name=?)");
              pre.setInt(1, uid);
              pre.setString(2, module);
              }
              else
              {
              pre=con.prepareStatement("select count(access_permission) from user_access_permission where user_id=? and module_id=(select module_id from module where module_name=?)");   
              pre.setInt(1, uid);
              pre.setString(2, module);
              }
              ResultSet rs=pre.executeQuery();
              if(rs.next())
              {
                  if(rs.getInt(1)>0)
                  {
                      i=true;
                  }
                 else
                  {
                      i=false;
                  }
              }
              else
              {
                  i=false;
              }
          }
          catch(Exception e)
          {
              i=false;
          }
          finally
          {
              con.close();
          }
          return i;
      }
       
       public static boolean getModuleStatus(int id,int uid) throws SQLException
       {
           boolean i=false;
           try
           {
             con=DBConnection.Connection.getConnection();
             PreparedStatement pre=con.prepareStatement("select count(*) from user_access_permission where module_id=? and access_permission=1 and user_id=?");
             pre.setInt(1, id);
             pre.setInt(2,uid);
             ResultSet rs=pre.executeQuery();
             while(rs.next())
             {
                 if(rs.getInt(1)>0)
                 {
                     i=true;
                 }
                 else
                 {
                     i=false;
                 }
             }
           }
           catch(Exception e)
           {
               
           }
           finally
           {
               con.close();
           }
           return i;
       }
       public static Vector viewPermission(int uid)throws SQLException{
           Vector<Object> list=new Vector<Object>();
           try{
               con=DBConnection.Connection.getConnection();
               PreparedStatement pre=con.prepareStatement("SELECT * from USER_ACCESS_PERMISSION uap left join MODULE m on uap.MODULE_ID=m.MODULE_ID left join SUB_MODULE sm on uap.SUB_MODULE_ID=sm.SUB_MODULE_ID left join module mod on sm.module_id=mod.module_id where uap.USER_ID=? AND ACCESS_PERMISSION=1");
               pre.setInt(1, uid);
               ResultSet rs=pre.executeQuery();
               while(rs.next()){
                   UserAccessPermissionBean upb=new UserAccessPermissionBean();
                   ModuleBean mb=new ModuleBean();
                   SubModuleBean smb=new SubModuleBean();
                   ModuleBean mod=new ModuleBean();
                   
                   upb.setUserAcessId(rs.getInt(1));
                   upb.setUserId(rs.getInt(2));
                   upb.setModuleId(rs.getInt(3));
                   upb.setSubModuleId(rs.getInt(4));
                   upb.setAcessStatus(rs.getInt(5));
                   upb.setEntryDate(rs.getDate(6));
                   mb.setModuleId(rs.getInt(7));
                   mb.setModuleName(rs.getString(8));
                   mb.setEntryDate(rs.getDate(9));
                   upb.setModuleBean(mb);
                   smb.setSubModuleId(rs.getInt(10));
                   smb.setSubModuleName(rs.getString(11));
                   smb.setModuleId(rs.getInt(12));
                   smb.setEntryDate(rs.getDate(13));
                   mod.setModuleId(rs.getInt(7));
                   mod.setModuleName(rs.getString(8));
                   mod.setEntryDate(rs.getDate(9));
                   smb.setModuleBean(mb);
                   upb.setSubModuleBean(smb);
                   list.add(upb);
               }
           }catch(Exception e){
               System.out.println(e);
           }finally{
               con.close();
           }return list;
       }
       public static Vector viewDistinctModule(int uid) throws SQLException {
            Vector<Object> list=new Vector<Object>();
            try {
            con=DBConnection.Connection.getConnection();
             PreparedStatement pre=con.prepareStatement("SELECT distinct(module_id) from USER_ACCESS_PERMISSION uap where uap.USER_ID=? AND ACCESS_PERMISSION=1");
             pre.setInt(1, uid);
             ResultSet rs=pre.executeQuery();
             while(rs.next())
             {
               UserAccessPermissionBean mb=new UserAccessPermissionBean();
               mb.setModuleId(rs.getInt(1));
               list.add(mb);
             }
            }
        catch (Exception e) 
        {
            System.out.println(e);
        }
        finally
        {
            con.close();
        }
        return list;
    }
    public static Vector viewSpecificSubModule(int uid, int modid) throws SQLException {
            Vector<Object> list=new Vector<Object>();
            try {
            con=DBConnection.Connection.getConnection();
             PreparedStatement pre=con.prepareStatement("SELECT SUB_MODULE_ID from USER_ACCESS_PERMISSION where USER_ID=? and module_id=? AND ACCESS_PERMISSION=1");
             pre.setInt(1, uid);
             pre.setInt(2, modid);
             ResultSet rs=pre.executeQuery();
             while(rs.next())
             {
               UserAccessPermissionBean apb=new UserAccessPermissionBean();
               apb.setSubModuleId(rs.getInt(1));
               list.add(apb);
             }
            }
        catch (Exception e) 
        {
            System.out.println(e);
        }
        finally
        {
            con.close();
        }
        return list;
    } 
}
